﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace HRMService
{
    // NOTE: If you change the class name "Employee" here, you must also update the reference to "Employee" in App.config.
    public partial class MainService : IDepartment
    {
        public int Department_Using(int DepartmentID)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Department_Using", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@DepartmentID", DepartmentID);
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Department_GetByCode(string _DepartmentCode, string _BranchCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Department_GetByCode", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Department_Code", _DepartmentCode);
            dbCmd.Parameters.AddWithValue("@Branch_Code", _BranchCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Department_GetAll()
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Department_GetAll", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Department_GetWithBranch(int BranchID, int _Type)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Department_GetWithBranch", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", BranchID);
            dbCmd.Parameters.AddWithValue("@Type", _Type);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Department_GetAllWithBranch(int BranchID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Department_GetAllWithBranch", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", BranchID);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Department_GetWitchMasterID(int DepartmentID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Department_GetWithMasterID", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@DepartmentID", DepartmentID);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Department_GetList(out int ItemCount, int branchID, int currentPage, int pageSize)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Department_GetList", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", branchID);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DepartmentInfo Department_GetInfo(int DepartmentID)
        {
            DepartmentInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Department_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Department_ID", DepartmentID);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new DepartmentInfo();
                    retVal.ID = Convert.ToInt32(dr["Department_ID"]);
                    retVal.BranchID = Convert.ToInt32(dr["Department_BranchID"]);
                    retVal.MasterID = Convert.ToInt32(dr["Department_MasterID"]);
                    retVal.Code = dr["Department_Code"].ToString();
                    retVal.Description = dr["Department_Description"].ToString();
                    retVal.Name = dr["Department_Name"].ToString();
                    retVal.IsHRM = dr["Department_IsHRM"] != DBNull.Value ? Convert.ToBoolean(dr["Department_IsHRM"]) : false;
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public int Department_Insert(DepartmentInfo eInfo)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Department_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Department_Name", eInfo.Name);
            dbCmd.Parameters.AddWithValue("@Department_Description", eInfo.Description);
            dbCmd.Parameters.AddWithValue("@Department_Code", eInfo.Code);
            dbCmd.Parameters.AddWithValue("@Department_BranchID", eInfo.BranchID);
            dbCmd.Parameters.AddWithValue("@Department_MasterID", eInfo.MasterID);
            dbCmd.Parameters.AddWithValue("@Department_IsHRM", eInfo.IsHRM);
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool Department_Update(DepartmentInfo eInfo)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Department_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Department_ID", eInfo.ID);
            dbCmd.Parameters.AddWithValue("@Department_Name", eInfo.Name);
            dbCmd.Parameters.AddWithValue("@Department_Description", eInfo.Description);
            dbCmd.Parameters.AddWithValue("@Department_Code", eInfo.Code);
            dbCmd.Parameters.AddWithValue("@Department_BranchID", eInfo.BranchID);
            dbCmd.Parameters.AddWithValue("@Department_MasterID", eInfo.MasterID);
            dbCmd.Parameters.AddWithValue("@Department_IsHRM", eInfo.IsHRM);
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool Department_Delete(int DepartmentID)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Department_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Department_ID", DepartmentID);

            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
    }
}

